package com.ayic.garbagerecycle.mapper;

import com.ayic.garbagerecycle.pojo.AnalysisData;
import com.ayic.garbagerecycle.pojo.Station;
import com.ayic.garbagerecycle.pojo.StationInfoLite;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface StationMapper {

    // 分页查询
    @Select("SELECT station_id AS stationId, station_name AS stationName, location, capacity, current_capacity AS currentCapacity, created_at AS createdAt, updated_at AS updatedAt " +
            "FROM stations LIMIT #{offset}, #{pageSize}")
    List<Station> selectByPage(@Param("offset") int offset, @Param("pageSize") int pageSize);

    // 站点计数
    @Select("SELECT COUNT(*) FROM stations")
    int count();

    // 根据站点ID 查询站点信息
    @Select("select station_id stationId, station_name stationName, location, capacity,current_capacity currentCapacity, updated_at updatedAt, user_id userId from stations where station_id = #{stationId}")
    Station selectById(Integer stationId);

    // 更新站点信息
    @Update("UPDATE stations s SET current_capacity = ( SELECT IFNULL(SUM(volume), 0) FROM waste_records WHERE station_id = #{stationId} ) WHERE station_id = #{stationId}")
    void updateCurrentCapacity(Integer stationId);

    // 根据用户名统计站点总数
    @Select("SELECT COUNT(DISTINCT s.station_id) " +
            "FROM stations s " +
            "JOIN waste_records wr ON s.station_id = wr.station_id " +
            "JOIN users u ON wr.user_id = u.user_id " +
            "WHERE u.username = #{username}")
    int countByUsername(String username);

    // 管理员专用， 获取全部站点信息
    @Select("SELECT station_id AS stationId, station_name AS stationName, location, capacity, current_capacity AS currentCapacity, created_at AS createdAt, updated_at AS updatedAt FROM stations order by stationId")
    List<Station> selectAll();

    /**
     * 获取所有站点的location字段
     * @return 所有站点的location列表
     */
    @Select("SELECT location FROM stations")
    List<String> getAllStationLocations();

    /**
     * 根据区域列表查询站点信息
     * @param areas 区域名称列表
     * @return 匹配的站点列表
     */
    List<StationInfoLite> selectByLocations(@Param("areas") List<String> areas);


    List<AnalysisData> selectLast30DaysByStationId(@Param("stationIds") List<Integer> stationIds);

    @Select("SELECT\n" +
            "        wr.station_id AS stationId,\n" +
            "        s.station_name AS stationName,\n" +
            "        DATE(wr.created_at) AS createdAt,\n" +
            "        SUM(wr.weight) AS totalWeight,\n" +
            "        SUM(wr.volume) AS totalVolume,\n" +
            "        SUM(wr.value) AS totalValue\n" +
            "        FROM waste_records wr\n" +
            "        JOIN stations s ON wr.station_id = s.station_id\n" +
            "        WHERE wr.created_at >= CURDATE() - INTERVAL 30 DAY\n" +
            "        AND wr.station_id  = #{stationId}" +
            "        GROUP BY wr.station_id, DATE(wr.created_at)\n" +
            "        ORDER BY wr.station_id, createdAt")
    List<AnalysisData> selectLast30DaysBySingleStationId(Integer stationId);

    @Update("UPDATE stations SET current_capacity = 0 WHERE station_id = #{stationId}")
    void cleanStationCapacity(Integer stationId);

    /**
     * 根据用户名查询负责的站点列表
     * @param username 用户名
     * @return 站点列表
     */
    @Select("SELECT DISTINCT s.* FROM stations s " +
            "JOIN waste_records wr ON s.station_id = wr.station_id " +
            "JOIN users u ON wr.user_id = u.user_id " +
            "WHERE u.username = #{username}")
    List<Station> selectByUsername(String username);
}
